package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Comment;
import com.wanmait.spacesys.vo.Userinfo;

public class CommentDAO {
	//回复
	public void reple(Integer id,String repleContent,Userinfo userinfo,Integer pid) {
		String sql = "INSERT INTO comment (content,post_id,userinfo_id,pid) VALUES (?,?,?,?)";
		DbHelper.update(sql, repleContent,id,userinfo.getId(),pid);
	}
	//查找被举报人的id
	public int beReportUser(Integer id) {
		int count=0;
		String sql = "SELECT comment.id,comment.userinfo_id,userinfo.id,userinfo.nickname FROM comment "
				+ "LEFT JOIN userinfo ON userinfo.id=comment.userinfo_id "
				+ "WHERE comment.id=? ";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs= ps.executeQuery();
			rs.next();
			count = rs.getInt("userinfo.id");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	//举报
	public void report(Integer id,Integer userid,String because,Integer beReportId) {
		String sql = "INSERT INTO report(comment_id,userinfo_id,reason) VALUES (?,?,?)";
		DbHelper.update(sql, id,userid,because);
		String sqltwo ="UPDATE userinfo SET userinfo.report=userinfo.report+1 WHERE userinfo.id=?";
		DbHelper.update(sqltwo, beReportId);
	}
	//判断发布评论时处不处于被禁言期间
	public int spack(Integer userid) {
		int count = 0;
		String sql = "SELECT ((datediff(NOW(),start_time))-set_time) AS `time` FROM spack "
				+ "WHERE userinfo_id="+userid;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt("time");				
			}			
		} catch (SQLException e) {
			//e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
		
	}
	//发布评论
	public void insert(Comment comment) {
		String sql = "INSERT INTO comment(post_id,content,userinfo_id) VALUES (?,?,?)";
		DbHelper.update(sql, comment.getPostId(),
				comment.getContent(),
				comment.getUserinfo().getId());
	}
	//根据评论pid查找下级评论
	public List<Comment> findByPid(Integer id){
		List<Comment> comments = new ArrayList<>();
		String sql = "SELECT comment.id AS A,content,post_id,nickname,userinfo_id, "
				+ "pid,comment_time,header_pic "
				+ "FROM comment "
				+ "LEFT JOIN userinfo AS B ON comment.userinfo_id=b.id "
				+ "where post_id="+id
				+ " order by comment.id ";
		List<Map<String,Object>> datas = DbHelper.select(sql);
		for(Map<String,Object> row : datas) {
			Comment comment = new Comment();
			comment.setId((Integer)row.get("id"));
			comment.setContent((String)row.get("content"));
			comment.setPostId((Integer)row.get("post_id"));
			comment.setPid((Integer)row.get("pid"));
			comment.setCommentTime((Timestamp)row.get("comment_time"));
			
			Userinfo userinfo = new Userinfo();
			userinfo.setId((Integer)row.get("userinfo_id"));
			userinfo.setNickname((String)row.get("nickname"));
			userinfo.setHeaderPic((String)row.get("header_pic"));
			comment.setUserinfo(userinfo);
			comments.add(comment);
		}
		ArrayList<Comment> rootComments = new ArrayList<>();
		HashMap<Integer,Comment> CommentIdMap = new HashMap<>();
		for(Comment comment:comments) {
			CommentIdMap.put(comment.getId(), comment);
		}
		for(Comment comment:comments) {
			if(comment.getPid()==null) {
				rootComments.add(comment);
				continue;
			}	
			Comment parentComment = CommentIdMap.get(comment.getPid());
			if(parentComment.getSubComment()==null) {
				parentComment.setSubComment(new ArrayList<Comment>());
			}
			parentComment.getSubComment().add(comment);

		}
		return rootComments;
	}
	//查找父级评论的昵称
	public String upCommentName(Integer pid) {
		String str = "";
		String sql ="SELECT comment.id,userinfo.nickname FROM comment "
				+ "LEFT JOIN userinfo ON userinfo_id=userinfo.id "
				+ "WHERE comment.id="+pid;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			str = rs.getString("nickname");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return str;
	}
	//根据帖子id查找一级评论
	public List<Comment> findAll(Integer id){ 
		String sql = "SELECT comment.id,userinfo_id,content,post_id,userinfo.nickname,"
				+ "pid,DATE_FORMAT(comment_time,'%Y-%m-%d')AS comment_time,"
				+ "userinfo.id,userinfo.header_pic FROM comment "
				+ "LEFT JOIN userinfo ON comment.userinfo_id=userinfo.id "
				+ "where post_id="+id+" and comment.pid is null";
		List<Comment> comments = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Comment comment = new Comment();
				comment.setId(rs.getInt("id"));
				comment.setContent(rs.getString("content"));
				comment.setPostId(rs.getInt("post_id"));
				comment.setPid(rs.getInt("pid"));
				comment.setCommentTime(rs.getTimestamp("comment_time"));
				
				Userinfo userinfo = new Userinfo();
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setHeaderPic(rs.getString("header_pic"));
				comment.setUserinfo(userinfo);
				
				comments.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return comments;
	}
}
